﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;
using System.Web.Configuration;
using System.Data;

namespace ProdeBackEnd.Libs
{
    public class Persistencia
    {
        private static Persistencia instance;
        private String dataBase;
        private String server;
        private String user;
        private String Password;
        private String connectionString;
        private SqlConnection con;
        private SqlCommand command;
        private SqlTransaction transaction;

        public static Persistencia getInstance()
        {
            if (instance == null)
            {
                instance = new Persistencia();
            }
            return instance;
        }

        private Persistencia()
        {
            Configuration rootWebConfig1 = WebConfigurationManager.OpenWebConfiguration("~/");
            if (rootWebConfig1.AppSettings.Settings.Count > 0)
            {
                this.dataBase = rootWebConfig1.AppSettings.Settings["BaseDatos"].Value;
                this.server = rootWebConfig1.AppSettings.Settings["Servidor"].Value;
                this.user = rootWebConfig1.AppSettings.Settings["usuario"].Value;
                this.Password = rootWebConfig1.AppSettings.Settings["Password"].Value;

                if (this.dataBase.Trim().Equals("") || this.server.Trim().Equals("") || this.user.Trim().Equals("") || this.Password.Trim().Equals(""))
                {
                    throw new BusinessException("201", "Error en la configuracion del servidor de aplicaciones");
                }
                else
                {
                    this.connectionString = makeConnectionString();
                    this.con = new SqlConnection();
                    this.con.ConnectionString = this.connectionString;
                    this.con.Open();
                    this.command = con.CreateCommand();
                    this.command.Connection = con;
                }
            }
            else
            {
                throw new BusinessException("202", "No se encontro el archivo de configuracion del servidor de aplicaciones");
            }

        }

        private String makeConnectionString()
        {
            //Provider=SQLOLEDB.1;Password=grexco02;Persist Security Info=True;User ID=sa;Initial Catalog=GxSegn;Data Source=GX2
            String cadena = "";
            cadena = cadena + ";Password=" + Password;
            cadena = cadena + ";Persist Security Info=True";
            cadena = cadena + ";User ID=" + user;
            cadena = cadena + ";Initial Catalog=" + dataBase;
            cadena = cadena + ";Data Source=" + server;
            return cadena;
        }

        public DataTable executeQuery(String sql)
        {
            try
            {
                SqlDataAdapter sda = new SqlDataAdapter(sql, this.con);
                DataSet ds = new DataSet("NuevoDataSet");
                sda.Fill(ds, "Tabla_1");
                DataTable tabla = ds.Tables[0];
                return tabla;
            }
            catch (Exception e)
            {
                throw new BusinessException("203", "Error executeQuery: " + e.Message);
            }
        }
        public String executeQueryXML(String sql)
        {
            try
            {
                SqlDataAdapter sda = new SqlDataAdapter(sql, this.con);
                DataSet ds = new DataSet("NuevoDataSet");
                sda.Fill(ds, "Tabla_1");
                return ds.GetXml();
            }
            catch (Exception e)
            {
                throw new BusinessException("204", "Error executeQueryXML: " + e.Message);
            }
        }

        public String ExecuteNonQuery(String sql)
        {
            try
            {
                string filasAfectadas = "0";
                SqlCommand cmSQL = new SqlCommand(sql, this.con);
                filasAfectadas = cmSQL.ExecuteNonQuery() + "";
                return filasAfectadas;
            }
            catch (Exception e)
            {
                throw new BusinessException("205", "Error ExecuteNonQuery: " + e.Message);
            }
        }

        public void openTransaction()
        {
            this.transaction = con.BeginTransaction();
            this.command.Transaction = transaction;
        }
        public void rolback()
        {
            this.transaction.Rollback();
        }

        public void commit()
        {
            this.transaction.Commit();
        }

        public void closeConnection()
        {
            this.con.Close();
        }
    }
}